TD 2 : SQL
SQL Interrogation de données
L3 MIASHS |
| Année 2025 |
Objectifs
- requêtes avec jointures internes,
- requêtes avec
WITH(CTEs).
Prérequis
schéma
world,cours Algèbre relationnelle,
cours Requêtes simples,
cours Requêtes imbriquées, CTEs.
Questions
- Quels sont les noms des capitales Sud-Américaines ? (14 lignes)
SELECT name
FROM world.city JOIN world.country ON capital=id
WHERE continent = 'South America';- Quels sont les noms des pays où le français est langue officielle ? (18 lignes)
SELECT name_country
FROM world.country JOIN world.countrylanguage USING (countrycode)
WHERE isofficial AND language='French';- Quels sont les pays dont le gouvernement est monarchique (constitutionnelle ou pas) ? (43 lignes)
SELECT name_country
FROM world.country
WHERE governmentform LIKE '%Monarchy%';- Quelles sont les monarchies d’Europe dont la capitale compte moins d’un million d’habitants ? (8 lignes)
WITH T1 AS (
SELECT *
FROM world.country
WHERE governmentform LIKE '%Monarchy%' AND continent='Europe'
)
SELECT name_country
FROM T1 JOIN world.city ON capital=id
WHERE population < 1000000;- Quelles sont les régions où on ne trouve pas de monarchie ? (9 lignes)
WITH monarchy AS
(
SELECT region
FROM world.country
WHERE governmentform LIKE '%Monarchy%'
)
(
SELECT region
FROM world.country
)
EXCEPT
(
SELECT *
FROM monarchy
);- Quelles sont les langues qui ne sont langues officielles dans aucun pays ? (355 lignes)
WITH R AS (
SELECT language
FROM world.countrylanguage
WHERE isofficial
)
(
SELECT DISTINCT language
FROM world.countrylanguage
)
EXCEPT
(
SELECT *
FROM R
);- Quels sont les pays qui n’ont pas de langue officielle ? (49 lignes)
WITH R AS (
SELECT countrycode
FROM world.countrylanguage
WHERE isofficial
),
S AS (
SELECT countrycode
FROM world.country
EXCEPT
SELECT * FROM R
)
SELECT name_country
FROM world.country JOIN S USING (countrycode);- Quels sont les pays dont la seule langue officielle est le français ? (9 lignes)
WITH R AS(
SELECT DISTINCT T1.countrycode
FROM world.countrylanguage as T1 JOIN
world.countrylanguage as T2 ON
T1.countrycode = T2.countrycode AND T1.language <> T2.language
WHERE T1.isofficial AND T2.isofficial
),
S AS (
SELECT countrycode
FROM world.countrylanguage
WHERE language='French' AND isofficial
EXCEPT
SELECT * FROM R
)
SELECT name_country
FROM world.country JOIN S USING(countrycode);Ou bien :
WITH OnlyFrench AS (
(SELECT countrycode
FROM countrylanguage
WHERE isofficial AND language = 'French')
EXCEPT
(SELECT countrycode
FROM countrylanguage
WHERE isofficial AND language <> 'French')
)
SELECT name_country
FROM world.country JOIN OnlyFrench USING(countrycode);- Les noms des pays où le français n’est pas la seule langue officielle. (9 lignes)
WITH R AS (
SELECT DISTINCT T1.countrycode
FROM world.countrylanguage as T1
JOIN world.countrylanguage as T2
ON T1.countrycode = T2.countrycode
WHERE T1.language <> T2.language AND
T1.language = 'French' AND
T1.isofficial AND
T2.isofficial
)
SELECT name_country
FROM R JOIN world.country USING(countrycode);En utilisant INTERSECT :
WITH AlsoFrench AS (
(SELECT countrycode
FROM world.countrylanguage
WHERE isofficial AND language = 'French')
INTERSECT
(SELECT countrycode
FROM world.countrylanguage
WHERE isofficial AND language <> 'French')
)
SELECT name_country
FROM world.country JOIN AlsoFrench USING(countrycode);- Quelles sont les régions qui ne comportent qu’une seule forme de gouvernement ? (3 lignes)
WITH R AS (
SELECT T1.region
FROM world.country AS T1 JOIN
world.country AS T2 ON T1.region = T2.region
WHERE T1.governmentform <> T2.governmentform
)
SELECT region
FROM world.country
EXCEPT
SELECT *
FROM R;- Quelles sont les langues officielles des pays dont la capitale compte plus de 5 000 000 d’habitants ? (12 lignes)
SELECT DISTINCT language
FROM world.country co JOIN world.city ci ON co.capital=ci.id
JOIN world.countrylanguage cl ON co.countrycode=cl.countrycode
WHERE population > 5000000 and isofficial;- Quels sont les pays où au moins trois langues sont parlées chacune par strictement plus de \(10\%\) de la population ? (35 lignes)
WITH R AS (
SELECT T1.countrycode
FROM world.countrylanguage AS T1
JOIN world.countrylanguage AS T2 ON T1.countrycode = T2.countrycode
JOIN world.countrylanguage AS T3 ON T1.countrycode = T3.countrycode
WHERE T1.language <> T2.language AND
T2.language <> T3.language AND
T1.language <> T3.language AND
T1.percentage > 10 AND
T2.percentage > 10 AND
T3.percentage > 10
)
SELECT DISTINCT name_country
FROM world.country JOIN R USING(countrycode);ou
WITH tpc AS (
SELECT *
FROM world.countrylanguage
WHERE percentage > 10),
three AS (
SELECT DISTINCT t1.countrycode
FROM tpc AS t1
JOIN tpc AS t2 ON (t1.countrycode=t2.countrycode AND t1.language<t2.language)
JOIN tpc AS t3 ON (t1.countrycode=t3.countrycode AND t3.language>t2.language)
)
SELECT co.name_country
FROM world.country co JOIN three USING(countrycode);- Quelles sont les régions où il existe deux pays dont les espérances de vie diffèrent par au moins \(10\) ans ? (16 lignes)
SELECT DISTINCT T1.region
FROM world.country AS T1 JOIN world.country AS T2
ON T1.region = T2.region AND T1.countrycode <> T2.countrycode
WHERE ABS(T1.lifeexpectancy - T2.lifeexpectancy) >= 10;- Quels sont les pays où l’anglais et le français sont des langues officielles ? (3 lignes)
WITH R AS (
SELECT T1.countrycode
FROM world.countrylanguage AS T1 JOIN
world.countrylanguage AS T2 USING (countrycode)
WHERE T1.language='French' AND
T2.language='English' AND
T1.isofficial AND
T2.isofficial
)
SELECT name_country
FROM R JOIN world.country USING (countrycode);- Montrer comment calculer l’intersection de deux tables avec une jointure (sous certaines conditions).
Si R et S sont deux tables de même schéma, on pourrait croire qu’une jointure naturelle des deux tables permet de calculer leur intersection.
Pourtant, du fait de la logique trivalente, si certaines colonnes contiennent des valeurs nulles
(SELECT * FROM R)
INTERSECT
(SELECT * FROM S) ;ne donne pas le même résultat que
SELECT *
FROM R NATURAL JOIN S ;Dans la deuxième requêtes, tous les tuples possédant au moins un attribut NULL sont rejetés car la condition de jointure est alors UNKNOWN.
Il faut limiter la jointure aux attributs qui identifient chaque ligne d’une table, ce qu’on appelle la clé primaire, pour lesquels la définition de la table interdit la valeur NULL.
Par exemple pour l’intersection de la table country avec elle-même :
(SELECT * FROM country)
INTERSECT
(SELECT * FROM country);retourne la table country, soit 239 lignes.
La requête
SELECT *
FROM country AS c1 JOIN
country AS c2
ON c1.countrycode = c2.countrycode;retourne égalemement les 239 lignes de la table country.
Mais la jointure naturelle
SELECT *
FROM country AS c1 NATURAL JOIN
country AS c2;ne donne que 167 lignes, les tuples qui n’ont aucun attribut NULL.